package com.bw.mapper;

import com.bw.bean.Vo;
import com.bw.bean.Vo1;
import com.bw.bean.Vo2;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Select;

import java.util.List;

/**
 * @Author:
 * @Date: 2025/3/15 14:40
 * @Description:
 **/
@Mapper
public interface VoMapper {

    @Select("select bp.name,sum(final_total_amount)value\n" +
            "from base_province bp\n" +
            "left join order_info oi on bp.id = oi.province_id\n" +
            "where final_total_amount is not null\n" +
            "group by bp.name")
    List<Vo> getList1();

    @Select("select bt.name,sum(final_total_amount)value from base_trademark bt\n" +
            "left join spu_info si on bt.id = si.tm_id\n" +
            "left join sku_info s on si.id = s.spu_id\n" +
            "left join order_detail od on s.id = od.sku_id\n" +
            "left join order_info oi on oi.id = od.order_id\n" +
            "where final_total_amount is not null\n" +
            "group by bt.name")
    List<Vo> getList2();

    @Select("select bc1.name,sum(final_total_amount)value from order_info oi\n" +
            "left join order_detail od on oi.id = od.order_id\n" +
            "left join sku_info si on si.id = od.sku_id\n" +
            "left join base_category3 b on si.category3_id = b.id\n" +
            "left join base_category2 c on b.category2_id = c.id\n" +
            "left join base_category1 bc1 on c.category1_id = bc1.id\n" +
            "group by bc1.name")
    List<Vo> getList3();

    @Select("select '用户数（人）' type,newNum,oldNum from\n" +
            "(select count(*)newNum from user_info where timestampdiff(year,create_time,now())<1)a,\n" +
            "(select count(*)oldNum from user_info where timestampdiff(year,create_time,now())>=1)b")
    List<Vo1> getList4();

    @Select("select bc2.name,sum(final_total_amount)value from base_category2 bc2\n" +
            "left join base_category3 b on bc2.id = b.category2_id\n" +
            "left join sku_info si on b.id = si.category3_id\n" +
            "left join order_detail od on si.id = od.sku_id\n" +
            "left join order_info oi on od.order_id = oi.id\n" +
            "where final_total_amount is not null\n" +
            "group by bc2.name\n" +
            "order by value desc")
    List<Vo> getList5();

    @Select("select concat(substr(birthday,3,1),'0后')name,sum(final_total_amount)value from user_info u\n" +
            "left join order_info oi on u.id = oi.user_id\n" +
            "group by concat(substr(birthday,3,1),'0后')")
    List<Vo> getList6();

    @Select("select yys name,sum(final_total_amount)value from order_info\n" +
            "where yys is not null\n" +
            "group by yys")
    List<Vo> getList7();

    @Select("select bt.name,if(ui.gender='F','女','男')gender,sum(final_total_amount)value\n" +
            "from base_trademark bt\n" +
            "left join sku_info si on bt.id = si.tm_id\n" +
            "left join order_detail od on si.id = od.sku_id\n" +
            "left join order_info oi on oi.id = od.order_id\n" +
            "left join user_info ui on ui.id = oi.user_id\n" +
            "where final_total_amount is not null\n" +
            "group by bt.name,gender")
    List<Vo2> getList8();
}
